用 Python 处理 Excel 的 14 个常用操作! | 您所在的位置:网站首页 › vlookup in python dataframe › 用 Python 处理 Excel 的 14 个常用操作! |
自从学了Python后就逼迫用Python来处理Excel,所有操作用Python实现。目的是巩固Python,与增强数据处理能力。
数据是网上找到的销售数据,长这样: vlookup是excel几乎最常用的公式,一般用于两个表的关联查询等。所以我先把这张表分为两个表。 df1=sale[['订单明细号','单据日期','地区名称', '业务员名称','客户分类', '存货编码', '客户名称', '业务员编码', '存货名称', '订单号', '客户编码', '部门名称', '部门编码']] df2=sale[['订单明细号','存货分类', '税费', '不含税金额', '订单金额', '利润', '单价','数量']] 需求:想知道df1的每一个订单对应的利润是多少。 利润一列存在于df2的表格中,所以想知道df1的每一个订单对应的利润是多少。用excel的话首先确认订单明细号是唯一值,然后在df1新增一列写:=vlookup(a2,df2!a:h,6,0) ,然后往下拉就ok了。(剩下13个我就不写excel啦) 那用python是如何实现的呢? #查看订单明细号是否重复,结果是没。 df1["订单明细号"].duplicated().value_counts() df2["订单明细号"].duplicated().value_counts() df_c=pd.merge(df1,df2,on="订单明细号",how="left") 二、数据透视表需求:想知道每个地区的业务员分别赚取的利润总和与利润平均数。 pd.pivot_table(sale,index="地区名称",columns="业务员名称",values="利润",aggfunc=[np.sum,np.mean]) 三、对比两列差异因为这表每列数据维度都不一样,比较起来没啥意义,所以我先做了个订单明细号的差异再进行比较。 需求:比较订单明细号与订单明细号2的差异并显示出来。 sale["订单明细号2"]=sale["订单明细号"] #在订单明细号2里前10个都+1. sale["订单明细号2"][1:10]=sale["订单明细号2"][1:10]+1 #差异输出 result=sale.loc[sale["订单明细号"].isin(sale["订单明细号2"])==False] 四、去除重复值需求:去除业务员编码的重复值 sale.drop_duplicates("业务员编码",inplace=True) 五、缺失值处理先查看销售数据哪几列有缺失值。 #列的行数小于index的行数的说明有缺失值,这里客户名称3295000)] 七、 模糊筛选数据需求:筛选存货名称含有"三星"或则含有"索尼"的信息。 sale.loc[sale["存货名称"].str.contains("三星|索尼")] 八、分类汇总需求:北京区域各业务员的利润总额。 sale.groupby(["地区名称","业务员名称"])["利润"].sum() 九、条件计算需求:存货名称含“三星字眼”并且税费高于1000的订单有几个?这些订单的利润总和和平均利润是多少?(或者最小值,最大值,四分位数,标注差) sale.loc[sale["存货名称"].str.contains("三星")&(sale["税费"]>=1000)][["订单明细号","利润"]].describe() 十、删除数据间的空格 需求:删除存货名称两边的空格。 sale["存货名称"].map(lambda s :s.strip("")) 十一、数据分列需求:将日期与时间分列。 sale=pd.merge(sale,pd.DataFrame(sale["单据日期"].str.split(" ",expand=True)),how="inner",left_index=True,right_index=True) 十二、异常值替换首先用describe()函数简单查看一下数据有无异常值。 #可看到销项税有负数,一般不会有这种情况,视它为异常值。 sale.describe() 需求:用0代替异常值。 sale["订单金额"]=sale["订单金额"].replace(min(sale["订单金额"]),0) 十三、分组需求:根据利润数据分布把地区分组为:"较差","中等","较好","非常好" 首先,当然是查看利润的数据分布呀,这里我们采用四分位数去判断。 sale.groupby("地区名称")["利润"].sum().describe() 根据四分位数把地区总利润为[-9,7091]区间的分组为“较差”,(7091,10952]区间的分组为"中等" (10952,17656]分组为较好,(17656,37556]分组为非常好。 #先建立一个Dataframe sale_area=pd.DataFrame(sale.groupby("地区名称")["利润"].sum()).reset_index() #设置bins,和分组名称 bins=[-10,7091,10952,17656,37556] groups=["较差","中等","较好","非常好"] #使用cut分组 #sale_area["分组"]=pd.cut(sale_area["利润"],bins,labels=groups) 十四、根据业务逻辑定义标签需求:销售利润率(即利润/订单金额)大于30%的商品信息并标记它为优质商品,小于5%为一般商品。 sale.loc[(sale["利润"]/sale["订单金额"])>0.3,"label"]="优质商品" sale.loc[(sale["利润"]/sale["订单金额"]) |
CopyRight 2018-2019 实验室设备网 版权所有 |